#!/usr/bin/env python
# -*- coding:utf-8 -*-

'''
# Author:linjinting for 02010166
@file: mysql_connection.py
@time: 2020/7/17 21:54
'''

from .mysql_pool import MysqlPool, MySQLError
from .generate_sql import SqlGenerate
from unit import find_caller
# import src.logging_model
import logging

logger = logging.getLogger("mysql.connection")


class MysqlConnection(object):

    def __init__(self, config, **kwargs):
        self.m_pool = None
        self.callback = kwargs.get('callback')
        self.m_remote_address = (config.get('host'), config.get('port'))
        # print(config)
        if config:
            config['port'] = int(config.get('port', 0))
            self.m_pool = MysqlPool(**config)
            self.print_and_logger(msg='[mysql] init pool\n', type='logger', level='debug',
                                  class_='mysql_client', caller=find_caller(), raddr=self.m_remote_address,
                                  )

        self.m_conn = None
        self.m_cursor = None
        self.m_isConnected = False

    def connect(self):
        try:
            self.m_conn = self.m_pool.getConn()
            self.m_isConnected = True
            self.print_and_logger(msg='[mysql] connect success \n', name='mysql_connect', type='event', level='info',
                                  class_='mysql_client', caller=find_caller(), raddr=self.m_remote_address, )
        except MySQLError as e:
            self.print_and_logger(msg='%s \n' % e.__str__(), name='mysql_close', type='event', level='error',
                                  class_='mysql_client', caller=find_caller(), raddr=self.m_remote_address, )

    def isConnected(self):
        return self.m_isConnected

    def select(self, sql_, param=None):
        # result = tuple()
        try:
            self.m_cursor = self.m_conn.cursor()
            rows = self.m_cursor.execute(sql_, param)
            if rows == 0:
                logger.debug('select: %s,%s rows:%d' % (sql_, param, rows))
                return
            logger.debug('[execute]%s, [result] rows %s' % (sql_, rows))
            result = self.m_cursor.fetchall()
            logger.debug(result)
            return result
        except MySQLError as e:
            logger.error(e)
            return
        finally:
            logger.debug("select close cursor")
            self.m_cursor.close()

    def insert(self, sql_, values):
        try:
            self.m_cursor = self.m_conn.cursor()
            ret = self.m_cursor.execute(sql_, values)
            logger.info("A total of %d row is affected" % ret)
            self.m_conn.commit()
            return ret
        except MySQLError as e:
            logger.error(e)
            self.m_conn.rollback()
            return -1
        finally:
            self.m_cursor.close()

    def insert_many(self, sql_, valuesList):
        try:
            self.m_cursor = self.m_conn.cursor()
            ret = self.m_cursor.executemany(sql_, valuesList)
            logger.info("A total of %d row is affected" % ret)
            self.m_conn.commit()
            return ret
        except MySQLError as e:
            logger.error(e)
            self.m_conn.rollback()
            return -1
        finally:
            self.m_cursor.close()

    def delete(self, sql, param=None):
        try:
            self.m_cursor = self.m_conn.cursor()
            ret = self.m_cursor.execute(sql, param)
            logger.info("delete rows %d" % ret)
            if ret == 0:
                return 0
            self.m_conn.commit()
            return ret
        except MySQLError as e:
            logger.error(e)
            self.m_conn.rollback()
            return -1
            # self.conn.rollback()
        finally:
            self.m_cursor.close()

    def update(self, sql, val):
        try:
            self.m_cursor = self.m_conn.cursor()
            ret = self.m_cursor.execute(sql, val)
            # print(ret)i
            if ret == 0:
                return 0
            self.m_conn.commit()
            return ret
        except MySQLError as e:
            logger.error(e)
            self.m_conn.rollback()
            return -1
            # self.conn.rollback()
        finally:
            self.m_cursor.close()

    def close(self):
        if self.m_cursor:
            try:
                self.m_cursor.close()
            except MySQLError as e:
                pass
        try:
            self.m_conn.close()
            self.print_and_logger(msg='[mysql] client close  \n', name='mysql_close', type='event', level='info',
                                  class_='mysql_client', caller=find_caller(), raddr=self.m_remote_address, )
        except MySQLError as e:
            self.print_and_logger(msg='%s \n' % e.__str__(), name='mysql_close', type='event', level='error',
                                  class_='mysql_client', caller=find_caller(), raddr=self.m_remote_address, )

    @staticmethod
    def genSelectSQL(table, fields_=None, filters=None):
        """
        # SELECT column_name,column_name
        # FROM table_name
        # [WHERE Clause]
        # [LIMIT N][ OFFSET M]
        :param table: table-name
        :param fields_: list
        :param filters:  example: [[field, value, "="]....]
        :return:
        """
        sql_header_ = SqlGenerate.mysql_gen_select_header(table, fields_)
        if sql_header_ is None:
            return
        if filters:
            sql_filter_, values = SqlGenerate.mysql_gen_filter_exp(filters)
            sql_ = SqlGenerate.mysql_gen_select(sql_header_, sql_filter_)
        else:
            sql_ = SqlGenerate.mysql_gen_select(sql_header_)
            values = ''
        logger.debug("genSelectSQL %s : %s" % (sql_, values))
        return sql_, values

    @staticmethod
    def genUpdateSQL(table, fieldMaps, filters):
        """
        # UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
        :param table: table-name
        :param fieldMaps: example: [{field1:new-value1},{field2:new-value2}] or {field1:new-value1,field2:new-value2}
        :param filters:
        :return: string
        """
        sql_header_ = SqlGenerate.mysql_gen_update_header(table)
        if sql_header_ is None:
            return
        sql_filter_, values = SqlGenerate.mysql_gen_filter_exp(filters)
        sql_assign = SqlGenerate.mysql_gen_assign_exp(fieldMaps)
        sql_ = SqlGenerate.mysql_gen_update(sql_header_, sql_assign, sql_filter_)
        logger.debug("genUpdateSQL %s : %s" % (sql_, values))
        return sql_, values

    @staticmethod
    def genInsertSQL(table, fieldMaps):
        """
        # INSERT INTO table_name ( field1, field2,...fieldN )
        #  VALUES
        #  ( value1, value2,...valueN )
        :param table:
        :param args: [{field1:value1},{field2:value2}....]
        :param kwargs: {field1:new-value1,field2:new-value2}
        :return:
        """
        sql_header_ = SqlGenerate.mysql_gen_insert_header(table)
        if sql_header_ is None:
            return
        sql_, values = SqlGenerate.mysql_gen_insert(sql_header_, fieldMaps)
        logger.debug("genInsertSQL %s : %s" % (sql_, values))
        return sql_, values

    @staticmethod
    def genInsertSQLMultiple(table, fileds, valueList):
        """
        # INSERT INTO table_name ( field1, field2,...fieldN )
        #  VALUES
        #  ( value1, value2,...valueN )
        :param table:
        :param args: [{field1:value1},{field2:value2}....]
        :param kwargs: {field1:new-value1,field2:new-value2}
        :return:
        """
        sql_header_ = SqlGenerate.mysql_gen_insert_header(table, fileds)
        if sql_header_ is None:
            return
        sql_, values = SqlGenerate.mysql_gen_insert_multiple(sql_header_, valueList)
        logger.debug("genInsertSQL %s : %s" % (sql_, values))
        return sql_, values

    @staticmethod
    def genDeleteSQL(table, filters=None):
        """

        :param table:
        :param filters:
        :return:
        """
        sql_header_ = SqlGenerate.mysql_gen_delete_header(table)
        if sql_header_ is None:
            return
        if filters:
            sql_filter_, values = SqlGenerate.mysql_gen_filter_exp(filters)
            sql_ = SqlGenerate.mysql_gen_delete(sql_header_, sql_filter_)
        else:
            sql_ = SqlGenerate.mysql_gen_delete(sql_header_)
            values = None

        logger.debug("genInsertSQL %s : %s" % (sql_, values))
        return sql_, values

    def print_and_logger(self, **kwargs):
        if self.callback:
            self.callback(**kwargs)
        else:
            print(kwargs)
